/**
 * @Copyright: Interactive Business Solution Pte Ltd, Singapore
 * @Author: Li Wen, 2007-11-24
 * @Version: 071124.001
 * Data access object of city model
 */
package com.ibsol.database
{
	import com.ibsol.model.City;
	
	import flash.data.SQLConnection;
	import flash.data.SQLStatement;
	import flash.events.SQLErrorEvent;
	import flash.events.SQLEvent;
	
	import mx.collections.ArrayCollection;
	import mx.controls.Alert;
	
	public class CityDAO
	{
		//Database connection
		private var _tourixDBConn:SQLConnection;
		/**
		 * City default constructor, gets tourix database connection
		 * @param: null
		 */
		public function CityDAO()
		{
			_tourixDBConn = DBConnManager.getConnection("tourix");
		}
		/**
		 * Load cities according to city name
		 * @param:	country:String			country name, cities belongs to
		 * 			resultHandler:Function	result function adapter, return city item array collection
		 * 			faultHandler:Function	fault function adapter
		 * @return:	void
		 */
		public function loadCitiesByCountry(country:String, resultHandler:Function, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "SELECT * FROM city WHERE country='" + country + "' and offlineOperator != 'DEL'";
			sqlStr.addEventListener(SQLEvent.RESULT,
				function (event:SQLEvent):void
				{
					resultHandler.call(this, typeArray(sqlStr.getResult().data));
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/**
		 * Load countries from table city
		 * @param:	resultHandler:Function	result function adapter, return city string array collection
		 * 		   	faultHandler:Function	fault function adapter
		 * @return:	void
		 */
		public function loadCountries(resultHandler:Function, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "SELECT DISTINCT country FROM city WHERE offlineOperator != 'DEL' ORDER BY country ASC";
			sqlStr.addEventListener(SQLEvent.RESULT,
				function (event:SQLEvent):void
				{
					resultHandler.call(this, countryArray(sqlStr.getResult().data));
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/**
		 * 
		 */
		public function loadCityNames(country:String, resultHandler:Function, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "SELECT id, name FROM city WHERE country = '" + country + "' WHERE offlineOperator != 'DEL' ORDER BY name ASC";
			sqlStr.addEventListener(SQLEvent.RESULT,
				function (event:SQLEvent):void
				{
					resultHandler.call(this, cityArray(sqlStr.getResult().data));
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/**
		 * Creates a new city into database. For different level of user, there are different options
		 * city (id, name, cityCode, country, continent, offlineOperator, updateTime)
		 * salesman level:		insert only, no need to set offlineOperator. Cannot update database with server.
		 * administrator level:
		 * manager level:		after insertion of city record, set offlineOperator to NEW. If there is a server
		 * 						connected, update data with server. Then reset offlineOperator.
		 * @param:				city:City				city object which will be inserted
		 * 						resultHandler:Function	result handler adapter
		 * 						faultHandler:Function	fault handler adapter
		 * @return:				void
		 */
		public function insertCity(city:City, resultHandler:Function = null, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "INSERT INTO city (name, cityCode, country, continent, offlineOperator, updateTime) " + 
					"VALUES (" + city.name + "','" + city.cityCode + "','" + city.country + "','" + city.continent + 
					"','NEW','T')";
			sqlStr.addEventListener(SQLEvent.RESULT,
				function (event:SQLEvent):void
				{
					city.id = sqlStr.getResult().lastInsertRowID;
					if(resultHandler != null)
						resultHandler.call(this);
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/**
		 * Updates a city record in database. For different level of user, there are different options
		 * city (id, name, cityCode, country, continent, offlineOperator)
		 * salesman level:			update only, no need to set offlineOperator. Cannot update database with server
		 * administrator level:
		 * manager level:			after update of photo record, set offlineOperator to EDIT. If there is a server
		 * 							connected, update data with serer. Then reset offlineOperator.
		 * @param:					city:City				city object which will be updated
		 * 							resultHandler:Function	result handler adapter
		 * 							faultHandler:Function	fault handler adapter
		 * @return:					void
		 */
		public function updateCity(city:City, resultHandler:Function = null, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "UPDATE city SET name='" + city.name + "',cityCode='" + city.cityCode + "',country='" + city.country + 
				"',continent='" + city.continent + "',offlineOperator='" + city.offlineOperator + "',updateTime='T' WHERE id=" + city.id;
			sqlStr.addEventListener(SQLEvent.RESULT,
				function (event:SQLEvent):void
				{
					if(resultHandler != null)
						resultHandler.call(this);
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/** THE FUNCTIONS ADDED BY LIU XIAO XIONG **/
		/**
		 * 按用户输入的城市名称查询城市的城市编码（cityCode），如果不存在，则返回null对象
		 * @ param cityName 城市名称
		 * @ param resultHandler 回调函数，存放一个city对象
		 * @ param faultHandler 错误函数
		 **/
		 public function getCityByName(cityName:String,resultHandler:Function,faultHandler:Function=null):void{
		 	var sqlState:SQLStatement=new SQLStatement();
		 	sqlState.sqlConnection=_tourixDBConn;
		 	sqlState.text="select * from city where name='"+cityName+"'";
		 	sqlState.addEventListener(SQLEvent.RESULT,function (e:SQLEvent):void{
		 		var arr:Array=sqlState.getResult().data;
		 		if(arr!=null&&arr.length>0)
		 			resultHandler.call(this,typeObject(arr[0]));
		 		else
		 			resultHandler.call(this,null);
		 	});
		 	sqlState.addEventListener(SQLErrorEvent.ERROR,faultHandler==null?sqlErrorHandler:faultHandler);
		 	sqlState.execute();
		 }
		 /**
		 * 模糊查询按用户输入的城市名称查询城市的城市编码（cityCode），如果不存在，则返回null对象
		 * @ param cityName 城市名称
		 * @ param resultHandler 回调函数，存放一个city对象
		 * @ param faultHandler 错误函数
		 **/
		 public function getCityByLikeName(cityName:String,resultHandler:Function,faultHandler:Function=null):void{
		 	var sqlState:SQLStatement=new SQLStatement();
		 	sqlState.sqlConnection=_tourixDBConn;
		 	sqlState.text="select * from city where name like'%"+cityName+"%'";
		 	sqlState.addEventListener(SQLEvent.RESULT,function (e:SQLEvent):void{
		 		resultHandler.call(this,typeArray(sqlState.getResult().data));
		 	});
		 	sqlState.addEventListener(SQLErrorEvent.ERROR,faultHandler==null?sqlErrorHandler:faultHandler);
		 	sqlState.execute();
		 }
		 /**
		 * 查询所有可用的城市信息 offlineOperator不是'DEL'
		 **/
		 public function getCityList(resultHandler:Function,faultHandler:Function=null):void{
		 	var sqlState:SQLStatement=new SQLStatement();
		 	sqlState.sqlConnection=_tourixDBConn;
		 	//sqlState.text="select * from city where offlineOperator<>'DEL' and offlineOperator<>'' and offlineOperator<>'null'";
		 	sqlState.text="select * from city";
		 	sqlState.addEventListener(SQLEvent.RESULT,function ():void{
		 		resultHandler.call(this,typeArray(sqlState.getResult().data));
		 	});
		 	sqlState.addEventListener(SQLErrorEvent.ERROR,faultHandler==null?sqlErrorHandler:faultHandler);
		 	sqlState.execute();
		 }
		/**
		 * Converts database object to City class
		 * @param:	o:Object	database return object
		 * @return:	City object after conversion
		 */
		public function typeObject(o:Object):City
		{
			var c:City = new City();
			c.id = o.id;
			c.name = o.name;
			c.cityCode = o.cityCode;
			c.country = o.country;
			c.continent = o.continent;
			c.offlineOperator = o.offlineOperator;
			c.updateTime = o.updateTime;
			return c;
		}
		/**
		 * Converts array object to City array collection
		 * @param: a:Array array of objects returned by SQL
		 * @return: ArrayCollection of Photo after conversion from Array
		 */
		private function typeArray(a:Array):ArrayCollection
		{
			if (a==null) return null;
			var ac:ArrayCollection = new ArrayCollection();
			for (var i:int; i<a.length; i++)
			{
				ac.addItem(typeObject(a[i]));
			}
			return ac;
		}
		
		private function countryArray(a:Array):ArrayCollection
		{
			if (a==null) return null;
			var ac:ArrayCollection = new ArrayCollection();
			for (var i:int; i<a.length; i++)
			{
				ac.addItem(String(a[i].country));
			}
			return ac;
		}
		
		private function cityArray(a:Array):ArrayCollection
		{
			if (a==null) return null;
			var ac:ArrayCollection = new ArrayCollection();
			for (var i:int; i<a.length; i++)
			{
				ac.addItem({label:String(a[i].name), id:uint(a[i].id)});
			}
			return ac;
		}
		/**
		 * Default SQL error handler, if above functions' fault handlers are null, this error handler is applied
		 * @param: event:SQLError	SQL Error event will be handled if there is an error
		 * @return: void
		 */
		private function sqlErrorHandler(event:SQLErrorEvent):void
		{
			mx.controls.Alert.show(event.text, "Error");
			trace(event.text);
		}
	}
}